package jsyf.oa.web.device;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by WHH-PC on 2017/11/28.
 */
public class ExportStockListExcel {
    private final Logger logger = LoggerFactory.getLogger(ExportStockListExcel.class);
    //显示的导出表的标题
    private String title;
    private String title1;
    private String title2;
    //导出表的列名
    private String[] rowName;
    private String[] rowName1;
    private String[] rowName2;

    private List<Object[]> dataList = new ArrayList<Object[]>();
    private List<Object[]> dataList1 = new ArrayList<Object[]>();
    private List<Object[]> dataList2 = new ArrayList<Object[]>();

    private HttpServletResponse  response;

    //构造方法，传入要导出的数据
    public ExportStockListExcel(String title,String title1,String title2, String[] rowName,String[] rowName1,String[] rowName2, List<Object[]> dataList,List<Object[]> dataList1,List<Object[]> dataList2, HttpServletResponse  response) {
        this.dataList = dataList;
        this.dataList1 = dataList1;
        this.dataList2 = dataList2;
        this.rowName = rowName;
        this.rowName1 = rowName1;
        this.rowName2 = rowName2;
        this.title = title;
        this.title1 = title1;
        this.title2 = title2;
        this.response = response;
    }

    /*
    * 导出数据
    * */
    public void export() throws Exception {
        try {
            HSSFWorkbook workbook =new HSSFWorkbook(); // 创建一个excel对象
            HSSFSheet sheet =workbook.createSheet(title); // 创建表格
            // 产生表格标题行
            sheet.setDefaultRowHeightInPoints(20);
            HSSFRow rowm  =sheet.createRow(0);  // 行
            HSSFCell cellTiltle =rowm.createCell(0);  // 单元格

            // sheet样式定义
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook); // 头样式
            HSSFCellStyle style = this.getStyle(workbook);  // 单元格样式
            /**
             * 参数说明
             * 从0开始   第一行 第一列 都是从角标0开始
             * 行 列 行列    (0,0,0,5)  合并第一行 第一列  到第一行 第六列
             * 起始行，起始列，结束行，结束列
             *
             * new Region()  这个方法使过时的
             */
            // 合并第一行的所有列
            sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (rowName.length-1)));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);

            int columnNum = rowName.length;  // 表格列的长度
            HSSFRow rowRowName = sheet.createRow(1);  // 在第二行创建行
            HSSFCellStyle cells =workbook.createCellStyle();
            cells.setBottomBorderColor(HSSFColor.BLACK.index);
            rowRowName.setRowStyle(cells);

            // 循环 将列名放进去
            for (int n = 0; n < columnNum; n++) {
                HSSFCell cellRowName = rowRowName.createCell(n);                //创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                cellRowName.setCellValue(text);                                    //设置列头单元格的值
                cellRowName.setCellStyle(columnTopStyle);
            }
            // 将查询到的数据设置到对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {
                Object[] obj = dataList.get(i);//遍历每个对象
                HSSFRow row = sheet.createRow(i + 2);//创建所需的行数
                for (int j = 0; j < obj.length; j++) {
                    HSSFCell cell = null;   //设置单元格的数据类型
                    cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                    if(j==2){
                        cell.setCellValue((obj[j].toString().length()>0) ? obj[j].toString()+"市" : "");
                    }else{
                        cell.setCellValue((obj[j] != null&&obj[j] != "") ? obj[j].toString() : "");
                    }
                    cell.setCellStyle(style);                                    //设置单元格样式
                }
            }
           /* //  让列宽随着导出的列长自动适应
            for(int i=0;i<columnNum;i++){
                sheet.autoSizeColumn((short)i);
//                sheet.setColumnWidth(i,sheet.getColumnWidth(i)*17/10);
                int colWidth = sheet.getColumnWidth(i)*17/10;
                if(colWidth<255*256){
                    sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
                }else{
                    sheet.setColumnWidth(i,6000 );
                }
            }*/
          //旧机标题及合并
            HSSFRow rowm1  =sheet.createRow(dataList.size()+2);  // 行
            HSSFCell cellTiltle1 =rowm1.createCell(0);  // 单元格

            sheet.addMergedRegion(new CellRangeAddress(dataList.size()+2, (short) dataList.size()+2, 0, (short) (rowName1.length-1)));
            cellTiltle1.setCellStyle(columnTopStyle);
            cellTiltle1.setCellValue(title1);
           //旧机字段行
            int columnNum1 = rowName1.length;  // 表格列的长度
            HSSFRow rowRowName1 = sheet.createRow(dataList.size()+3);  // 在第二行创建行
            HSSFCellStyle cells1 =workbook.createCellStyle();
            cells.setBottomBorderColor(HSSFColor.BLACK.index);
            rowRowName1.setRowStyle(cells1);
            for (int n = 0; n < columnNum1; n++) {
                HSSFCell cellRowName = rowRowName1.createCell(n);                //创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowName1[n]);
                cellRowName.setCellValue(text);                                    //设置列头单元格的值
                cellRowName.setCellStyle(columnTopStyle);
            }
            //旧机数据插入
            for (int i = 0; i < dataList1.size(); i++) {
                Object[] obj = dataList1.get(i);//遍历每个对象
                HSSFRow row = sheet.createRow(dataList.size()+4+i);//创建所需的行数
                for (int j = 0; j < obj.length; j++) {
                    HSSFCell cell = null;   //设置单元格的数据类型
                    cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                    if(j==2){
                        cell.setCellValue((obj[j].toString().length()>0) ? obj[j].toString()+"市" : "");
                    }else if(j==5){
                        cell.setCellValue((obj[j].toString().length()>0) ? obj[j].toString()+"年" : "");
                    }else{
                        cell.setCellValue((obj[j] != null&&obj[j] != "") ? obj[j].toString() : "");
                    }
                    cell.setCellStyle(style);//设置单元格样式
                }
            }
//旧房标题及合并
            HSSFRow rowm2  =sheet.createRow(dataList.size()+2+dataList1.size()+2);  // 行
            HSSFCell cellTiltle2 =rowm2.createCell(0);  // 单元格
            cellTiltle2.setCellStyle(columnTopStyle);
            cellTiltle2.setCellValue(title2);
            sheet.addMergedRegion(new CellRangeAddress(dataList.size()+2+dataList1.size()+2, (short) dataList.size()+2+dataList1.size()+2, 0, (short) (rowName2.length-1)));
            //旧房字段行
            int columnNum2 = rowName2.length;  // 表格列的长度
            HSSFRow rowRowName2 = sheet.createRow(dataList.size()+2+dataList1.size()+2+1);  // 在第二行创建行
            HSSFCellStyle cells2 =workbook.createCellStyle();
            cells.setBottomBorderColor(HSSFColor.BLACK.index);
            rowRowName2.setRowStyle(cells2);
            for (int n = 0; n < columnNum2; n++) {
                HSSFCell cellRowName = rowRowName2.createCell(n);                //创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
                //设置列头单元格的数据类型
                    HSSFRichTextString text = new HSSFRichTextString(rowName2[n]);
                    cellRowName.setCellValue(text);                                    //设置列头单元格的值
                    cellRowName.setCellStyle(columnTopStyle);

            }
            //旧房数据插入
            for (int i = 0; i < dataList2.size(); i++) {
                Object[] obj = dataList2.get(i);//遍历每个对象
                HSSFRow row = sheet.createRow(dataList.size()+4+dataList1.size()+2+i);//创建所需的行数
                row.setHeight((short)(row.getHeight()*16/10));
                for (int j = 0; j < obj.length; j++) {
                    if(j==9||j==10||j==11){
                    break;
                    }
                    HSSFCell cell = null;   //设置单元格的数据类型
                    cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                    String strText=obj[j].toString();
                    if(strText.length()>6){
                        StringBuilder sb = new StringBuilder(strText);//构造一个StringBuilder对象
                        if(j==6){
                            if(strText.length()>60){
                                sb.insert(60, "\r\n");//在指定的位置1，插入指定的字符串
                            }
                        }else if(j==4){
                            if(strText.length()>10){
                                sb.insert(10, "\r\n");//在指定的位置1，插入指定的字符串
                            }
                        }else{
                            sb.insert(6, "\r\n");//在指定的位置1，插入指定的字符串
                        }

                        strText = sb.toString();
                    }
                    cell.setCellValue(new HSSFRichTextString(strText)!= null ? new HSSFRichTextString(strText) : new HSSFRichTextString(""));
                    style.setWrapText(true);
                    cell.setCellStyle(style);//设置单元格样式
                }
                sheet.addMergedRegion(new CellRangeAddress(dataList.size()+4+dataList1.size()+2+i, (short) dataList.size()+4+dataList1.size()+2+i, 6, (short) (rowName.length-1)));
            }
            sheet.addMergedRegion(new CellRangeAddress(dataList.size()+2+dataList1.size()+2+1, (short) dataList.size()+2+dataList1.size()+2+1, 6, 8));
            for(int i=0;i<columnNum;i++){
                sheet.autoSizeColumn((short)i);
//                sheet.setColumnWidth(i,sheet.getColumnWidth(i)*17/10);
                int colWidth = sheet.getColumnWidth(i)*16/10;
                if(colWidth<255*256){
                    sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
                }else{
                    sheet.setColumnWidth(i,6000 );
                }
            }
           /* sheet.autoSizeColumn((short)0); //调整第一列宽度
            sheet.autoSizeColumn((short)1); //调整第二列宽度
            sheet.autoSizeColumn((short)2); //调整第三列宽度
            sheet.autoSizeColumn((short)3); //调整第四列宽度
            sheet.autoSizeColumn((short)4); //调整第五列宽度
            sheet.autoSizeColumn((short)5); //调整第六列宽度*/
            // 第七步，将文件输出到客户端浏览器
            if(workbook !=null){
                try
                {
                    // excel 表文件名
                    String fileName = "库存报表0级" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
                    String fileName11 = URLEncoder.encode(fileName,"UTF-8");
                    String headStr = "attachment; filename=\"" + fileName11 + "\"";
                    response.setContentType("APPLICATION/OCTET-STREAM");
                    response.setHeader("Content-Disposition", headStr);
                    OutputStream out = response.getOutputStream();
                    workbook.write(out);
                    out.flush();
                    out.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }

            }
        }catch(Exception e){
            e.printStackTrace();
            throw new Exception("导出信息失败！");
        }
    }

    /*
     * 列头单元格样式
     */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置单元格背景颜色
        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        return style;

    }

    /*
   * 列数据信息单元格样式
   */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;

    }


}
